#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

${HIVE_HOME} -S -e "
--todo dim_customer
--拉链导入T+1
--重建临时拉链表
DROP TABLE if EXISTS zx_dwd.dim_customer_tmp;
CREATE TABLE zx_dwd.customer_relationship_temp
(
    id                          STRING,
    create_date_time            STRING,
    update_date_time            STRING COMMENT '最后更新时间',
    deleted                     STRING COMMENT '是否被删除（禁用）',
    customer_id                 STRING COMMENT '所属客户id',
    first_id                    STRING COMMENT '第一条客户关系id',
    belonger                    STRING COMMENT '归属人',
    belonger_name               STRING COMMENT '归属人姓名',
    initial_belonger            STRING COMMENT '初始归属人',
    distribution_handler        STRING COMMENT '分配处理人',
    business_scrm_department_id STRING COMMENT '归属部门',
    last_visit_time             STRING COMMENT '最后回访时间',
    next_visit_time             STRING COMMENT '下次回访时间',
    origin_type                 STRING COMMENT '数据来源',
    itcast_school_id            STRING COMMENT '校区Id',
    itcast_subject_id           STRING COMMENT '学科Id',
    intention_study_type        STRING COMMENT '意向学习方式',
    anticipat_signup_date       STRING COMMENT '预计报名时间',
    level                       STRING COMMENT '客户级别',
    creator                     STRING COMMENT '创建人',
    current_creator             STRING COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                STRING COMMENT '创建者姓名',
    origin_channel              STRING COMMENT '来源渠道',
    comment                     STRING COMMENT '备注',
    first_customer_clue_id      STRING COMMENT '第一条线索id',
    last_customer_clue_id       STRING COMMENT '最后一条线索id',
    process_state               STRING COMMENT '处理状态',
    process_time                STRING COMMENT '处理状态变动时间',
    payment_state               STRING COMMENT '支付状态',
    payment_time                STRING COMMENT '支付状态变动时间',
    signup_state                STRING COMMENT '报名状态',
    signup_time                 STRING COMMENT '报名时间',
    notice_state                STRING COMMENT '通知状态',
    notice_time                 STRING COMMENT '通知状态变动时间',
    lock_state                  STRING COMMENT '锁定状态',
    lock_time                   STRING COMMENT '锁定状态修改时间',
    itcast_clazz_id             STRING COMMENT '所属ems班级id',
    itcast_clazz_time           STRING COMMENT '报班时间',
    payment_url                 STRING COMMENT '付款链接',
    payment_url_time            STRING COMMENT '支付链接生成时间',
    ems_student_id              STRING COMMENT 'ems的学生id',
    delete_reason               STRING COMMENT '删除原因',
    deleter                     STRING COMMENT '删除人',
    deleter_name                STRING COMMENT '删除人姓名',
    delete_time                 STRING COMMENT '删除时间',
    course_id                   STRING COMMENT '课程ID',
    course_name                 STRING COMMENT '课程名称',
    delete_comment              STRING COMMENT '删除原因说明',
    close_state                 STRING COMMENT '关闭装填',
    close_time                  STRING COMMENT '关闭状态变动时间',
    appeal_id                   STRING COMMENT '申诉id',
    tenant                      STRING COMMENT '租户',
    total_fee                   STRING COMMENT '报名费总金额',
    belonged                    STRING COMMENT '小周期归属人',
    belonged_time               STRING COMMENT '归属时间',
    belonger_time               STRING COMMENT '归属时间',
    transfer                    STRING COMMENT '转移人',
    transfer_time               STRING COMMENT '转移时间',
    follow_type                 STRING COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
    transfer_bxg_oa_account     STRING COMMENT '转移到博学谷归属人OA账号',
    transfer_bxg_belonger_name  STRING COMMENT '转移到博学谷归属人OA姓名'
) COMMENT '客户意向表'
    PARTITIONED BY (dt STRING)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '\t'
    STORED AS ORC
    TBLPROPERTIES ('orc.compress' = 'ZLIB');

--导入到临时拉链表
INSERT OVERWRITE TABLE zx_dwd.customer_relationship_temp PARTITION (start_date)
SELECT *
FROM (SELECT id,
             customer_relationship_id,
             create_date_time,
             update_date_time,
             deleted,
             name,
             idcard,
             birth_year,
             gender,
             phone,
             wechat,
             qq,
             email,
             area,
             leave_school_date,
             graduation_date,
             bxg_student_id,
             creator,
             origin_type,
             origin_channel,
             tenant,
             md_id,
             '9999-99-99' end_date,
             '${TD_DATE}' start_date
      FROM zx_ods.customer
      WHERE dt = '${TD_DATE}'
      UNION ALL
      SELECT c.id,
             customer_relationship_id,
             create_date_time,
             update_date_time,
             deleted,
             name,
             idcard,
             birth_year,
             gender,
             phone,
             wechat,
             qq,
             email,
             area,
             leave_school_date,
             graduation_date,
             bxg_student_id,
             creator,
             origin_type,
             origin_channel,
             tenant,
             md_id,
             IF(up.id IS NULL OR c.end_date < '9999-99-99', c.end_date, DATE_ADD('2022-05-18', -1)) end_date,
             c.start_date
      FROM zx_dwd.dim_customer c
               LEFT JOIN (SELECT id
                          FROM zx_ods.customer
                          WHERE dt = '${TD_DATE}') up ON c.id = up.id)
ORDER BY id, start_date;

--临时拉链表确认无误后，覆盖拉链表
INSERT OVERWRITE TABLE zx_dwd.dim_customer PARTITION (start_date)
SELECT *
FROM zx_dwd.dim_customer_tmp;
"